package com.bdbit.ChargingStation.dao.impl;

import com.bdbit.ChargingStation.dao.ChargingOrderDao;
import com.bdbit.ChargingStation.entity.ChargingOrder;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.*;

@Slf4j
@Repository
public class ChargingOrderDaoImpl implements ChargingOrderDao {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public ChargingOrderDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public int insert(ChargingOrder order) {
        String sql = "INSERT INTO charging_order (" +
            "user_id, order_year_month, status, pile_id, region_code, " +
            "ele_code, charging_kwh, unit_price, total_amount, start_time, " +
            "end_time, reported_duration, device_start_time, device_end_time, " +
            "prepay_amount, payment_method, payment_time, refund_amount, " +
            "refund_time, create_date, update_date, is_deleted" +
            ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?)";
        
        return jdbcTemplate.update(sql,
            order.getUserId(),
            order.getOrderYearMonth(),
            order.getStatus(),
            order.getPileId(),
            order.getRegionCode(),
            order.getEleCode(),
            order.getChargingKwh(),
            order.getUnitPrice(),
            order.getTotalAmount(),
            order.getStartTime(),
            order.getEndTime(),
            order.getReportedDuration(),
            order.getDeviceStartTime(),
            order.getDeviceEndTime(),
            order.getPrepayAmount(),
            order.getPaymentMethod(),
            order.getPaymentTime(),
            order.getRefundAmount(),
            order.getRefundTime(),
            order.getCreateDate(),
            order.getUpdateDate(),
            order.getIsDeleted()
        );
    }

    @Override
    public int update(ChargingOrder order) {
        String sql = "UPDATE charging_order SET " +
                "user_id = ?, order_year_month = ?, status = ?, pile_id = ?, " +
                "region_code = ?, ele_code = ?, charging_kwh = ?, unit_price = ?, " +
                "total_amount = ?, start_time = ?, end_time = ?, reported_duration = ?, " +
                "device_start_time = ?, device_end_time = ?, prepay_amount = ?, " +
                "payment_method = ?, payment_time = ?, refund_amount = ?, " +
                "refund_time = ?, create_date = ?, update_date = ?, is_deleted = ?, " +
                "pay_type = ?, payment_form = ?, charging_minutes = ?, order_id = ? " +
                "WHERE id = ?";
    
        return jdbcTemplate.update(sql,
                order.getUserId(),
                order.getOrderYearMonth(),
                order.getStatus(),
                order.getPileId(),
                order.getRegionCode(),
                order.getEleCode(),
                order.getChargingKwh(),
                order.getUnitPrice(),
                order.getTotalAmount(),
                order.getStartTime(),
                order.getEndTime(),
                order.getReportedDuration(),
                order.getDeviceStartTime(),
                order.getDeviceEndTime(),
                order.getPrepayAmount(),
                order.getPaymentMethod(),
                order.getPaymentTime(),
                order.getRefundAmount(),
                order.getRefundTime(),
                order.getCreateDate(),
                order.getUpdateDate(),
                order.getIsDeleted(),
                order.getPayType(),        // 添加缺失的参数
                order.getPaymentForm(),    // 添加缺失的参数
                order.getChargingMinutes(), // 添加缺失的参数
                order.getOrderId(),        // 添加缺失的参数
                order.getId()              // WHERE 条件的参数
        );
    }

    @Override
    public int delete(Long id) {
        String sql = "DELETE FROM charging_order WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public ChargingOrder findById(Long id) {
        String sql = "SELECT * FROM charging_order WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ChargingOrder.class), id);
    }

    @Override
    public List<ChargingOrder> findAll() {
        String sql = "SELECT * FROM charging_order WHERE is_deleted = 0";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ChargingOrder.class));
    }

    @Override
    public List<ChargingOrder> findByUserId(String userId) {
        String sql = "SELECT * FROM charging_order WHERE user_id = ? AND is_deleted = 0";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ChargingOrder.class), userId);
    }

    @Override
    public List<ChargingOrder> findByStatus(Integer status) {
        String sql = "SELECT * FROM charging_order WHERE status = ? AND is_deleted = 0";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ChargingOrder.class), status);
    }

    @Override
    public List<ChargingOrder> findByRegionAndEleCode(String regionCode, String eleCode) {
        String sql = "SELECT * FROM charging_order WHERE region_code = ? AND ele_code = ? AND is_deleted = 0";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ChargingOrder.class), regionCode, eleCode);
    }

    @Override
    public int updateStatus(Long id, Integer status) {
        String sql = "UPDATE charging_order SET status = ? WHERE id = ?";
        return jdbcTemplate.update(sql, status, id);
    }

    @Override
    public int logicalDelete(Long id) {
        String sql = "UPDATE charging_order SET is_deleted = 1 WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }



    // ChargingOrderDaoImpl.java 中添加
    @Override
    public List<ChargingOrder> searchOrders(Map<String, Object> params) {
        StringBuilder sql = new StringBuilder("SELECT * FROM charging_order WHERE is_deleted = 0");

        // 添加条件
        if (params.containsKey("userId")) {
            sql.append(" AND user_id = ?");
        }
        if (params.containsKey("status")) {
            sql.append(" AND status = ?");
        }
        if (params.containsKey("regionCode")) {
            sql.append(" AND region_code = ?");
        }
        if (params.containsKey("eleCode")) {
            sql.append(" AND ele_code = ?");
        }
        if (params.containsKey("startDate")) {
            sql.append(" AND create_date >= ?");
        }
        if (params.containsKey("endDate")) {
            sql.append(" AND create_date <= ?");
        }

        // 添加分页
        if (params.containsKey("size") && (Integer)params.get("size") > 0) {
            sql.append(" LIMIT ?, ?");
        }

        // 准备参数列表
        List<Object> paramList = new ArrayList<>();
        addParamIfPresent(paramList, params, "userId");
        addParamIfPresent(paramList, params, "status");
        addParamIfPresent(paramList, params, "regionCode");
        addParamIfPresent(paramList, params, "eleCode");
        addParamIfPresent(paramList, params, "startDate");
        addParamIfPresent(paramList, params, "endDate");

        // 添加分页参数
        if (params.containsKey("size") && (Integer)params.get("size") > 0) {
            paramList.add(params.get("offset"));
            paramList.add(params.get("size"));
        }

        // 执行查询
        return jdbcTemplate.query(
                sql.toString(),
                paramList.toArray(),
                new BeanPropertyRowMapper<>(ChargingOrder.class)
        );
    }

    @Override
    public List<ChargingOrder> searchOrders(Map<String, Object> params, int offset, int size) {
        return Collections.emptyList();
    }

    private void addParamIfPresent(List<Object> paramList, Map<String, Object> params, String key) {
        if (params.containsKey(key)) {
            paramList.add(params.get(key));
        }
    }

    @Override
    public long countSearchOrders(Map<String, Object> params) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM charging_order WHERE is_deleted = 0");

        // 添加条件
        if (params.containsKey("userId")) {
            sql.append(" AND user_id = ?");
        }
        if (params.containsKey("status")) {
            sql.append(" AND status = ?");
        }
        if (params.containsKey("regionCode")) {
            sql.append(" AND region_code = ?");
        }
        if (params.containsKey("eleCode")) {
            sql.append(" AND ele_code = ?");
        }
        if (params.containsKey("startDate")) {
            sql.append(" AND create_date >= ?");
        }
        if (params.containsKey("endDate")) {
            sql.append(" AND create_date <= ?");
        }

        // 准备参数列表
        List<Object> paramList = new ArrayList<>();
        addParamIfPresent(paramList, params, "userId");
        addParamIfPresent(paramList, params, "status");
        addParamIfPresent(paramList, params, "regionCode");
        addParamIfPresent(paramList, params, "eleCode");
        addParamIfPresent(paramList, params, "startDate");
        addParamIfPresent(paramList, params, "endDate");

        return jdbcTemplate.queryForObject(sql.toString(), Long.class, paramList.toArray());
    }

    // 构建查询条件的通用方法
    private void buildSearchConditions(StringBuilder sql, Map<String, Object> params, List<Object> values) {
        if (params.containsKey("userId")) {
            sql.append(" AND user_id = ?");
            values.add(params.get("userId"));
        }

        if (params.containsKey("status")) {
            sql.append(" AND status = ?");
            values.add(params.get("status"));
        }

        if (params.containsKey("regionCode")) {
            sql.append(" AND region_code = ?");
            values.add(params.get("regionCode"));
        }

        if (params.containsKey("eleCode")) {
            sql.append(" AND ele_code = ?");
            values.add(params.get("eleCode"));
        }

        if (params.containsKey("startDate")) {
            sql.append(" AND create_date >= ?");
            values.add(params.get("startDate"));
        }

        if (params.containsKey("endDate")) {
            sql.append(" AND create_date <= ?");
            values.add(params.get("endDate"));
        }
    }
    /**
     * 查找并返回所有活动状态的充电订单
     * 
     * 活动订单定义为状态为0或2且未标记为删除(is_deleted = 0)的订单
     * 此方法使用SQL查询从数据库中选择所有匹配的记录，并将它们映射为ChargingOrder对象的列表
     * 
     * @return 包含活动充电订单的列表
     */
    @Override
    public List<ChargingOrder> findActiveOrders() {
        // SQL查询以选择所有活动状态的充电订单
        //String sql = "SELECT * FROM charging_order WHERE status IN (0, 2) AND is_deleted = 0";
        String sql = "SELECT " +
                "t2.id id," +
                "t2.start_time startTime," +
                "t2.end_time endTime," +
                "t2.charging_minutes chargingMinutes," +
                "t2.prepay_amount PrepayAmount," +
                "t2.status " +
                " FROM charging_pile t1" +
                " LEFT JOIN charging_order t2 ON t1.iccid = t2.ele_code" +
                " WHERE t1.iccid IS NOT NULL" +
                " AND t2.status IN (0,2)" +
                " AND t1.last_time > NOW() - INTERVAL 1  MINUTE";
        log.info("查询以选择所有活动状态的充电订单SQL: {}", sql);
        // 执行查询并将结果映射为ChargingOrder对象的列表
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ChargingOrder.class));
    }

    @Override
    public List<ChargingOrder> findAllWithDateRange(Date startDate, Date endDate) {
        StringBuilder sql = new StringBuilder("SELECT * FROM charging_order WHERE is_deleted = 0");
        List<Object> params = new ArrayList<>();

        if (startDate != null) {
            sql.append(" AND create_date >= ?");
            params.add(startDate);
        }
        if (endDate != null) {
            sql.append(" AND create_date <= ?");
            params.add(endDate);
        }

        return jdbcTemplate.query(
                sql.toString(),
                params.toArray(),
                new BeanPropertyRowMapper<>(ChargingOrder.class)
        );
    }

    @Override
    public List<ChargingOrder> findByUserIdWithDateRange(String userId, Date startDate, Date endDate) {
        StringBuilder sql = new StringBuilder("SELECT * FROM charging_order WHERE is_deleted = 0 AND user_id = ?");
        List<Object> params = new ArrayList<>();
        params.add(userId);

        if (startDate != null) {
            sql.append(" AND create_date >= ?");
            params.add(startDate);
        }
        if (endDate != null) {
            sql.append(" AND create_date <= ?");
            params.add(endDate);
        }

        return jdbcTemplate.query(
                sql.toString(),
                params.toArray(),
                new BeanPropertyRowMapper<>(ChargingOrder.class)
        );
    }

    @Override
    public List<ChargingOrder> findByStatusWithDateRange(Integer status, Date startDate, Date endDate) {
        StringBuilder sql = new StringBuilder("SELECT * FROM charging_order WHERE is_deleted = 0 AND status = ?");
        List<Object> params = new ArrayList<>();
        params.add(status);

        if (startDate != null) {
            sql.append(" AND create_date >= ?");
            params.add(startDate);
        }
        if (endDate != null) {
            sql.append(" AND create_date <= ?");
            params.add(endDate);
        }

        return jdbcTemplate.query(
                sql.toString(),
                params.toArray(),
                new BeanPropertyRowMapper<>(ChargingOrder.class)
        );
    }
}